Winzipper/Winzipper_ISO.iso/programming/oracle7 7.2/DB/UTIL72/UTLESTAT.SQL

Text File | 1995-05-09 | 10.0 KB | 299 lines

rem
rem $Header: utlestat.sql 7020100.1 94/09/23 22:14:42 cli Generic<base> $ estat.sql
rem
Rem Copyright (c) 1988 by Oracle Corporation
Rem NAME
REM    UTLESTAT.SQL
Rem  FUNCTION
Rem    This script will generate a report (in "report.txt") which will contain
Rem    usefull information for performance monitoring.  In particular
Rem    information from v$sysstat, v$latch, and v$rollstat.
Rem  NOTES
Rem    Don't worry about errors during "drop table"s, they are normal.
Rem  MODIFIED
Rem     drady      09/09/93 -  merge changes from branch 1.1.312.2
Rem     drady      04/26/93 -  Stat name changes for 7.1
Rem     drady      03/22/93 -  merge changes from branch 1.1.312.1
Rem     drady      08/24/93 -  bug 173918
Rem     drady      03/04/93 -  fix bug 152986
Rem     glumpkin   11/23/92 -  Creation
Rem     glumpkin   11/23/92 -  Renamed from UTLSTATE.SQL
Rem     glumpkin   10/20/92 -  Renamed from ESTAT.SQL
Rem     jloaiza    03/26/92 -  add write queue query
Rem     jloaiza    02/24/92 -  fix latch stats
Rem     jloaiza    01/17/92 -  improve output
Rem     jloaiza    01/07/92 -  rework for version 7
Rem   Laursen    01/01/91 - V6 to V7 merge
Rem   Trabosh    09/27/89 - added order by and group by to stats$files
Rem   Loaiza     04/04/89 - fix run dates to do minutes instead of months
Rem   Loaiza     03/31/89 - add kqrst usage column
Rem   Jloaiza    03/16/89 - improve names and formats
Rem   Jloaiza    03/09/89 - make kqrst columns intelligible
Rem   Jloaiza    02/23/89 - changed table names, added dates
Rem   Martin     02/22/89 - Creation
set echo on;
connect internal;

Rem ********************************************************************
Rem                Gather Ending Statistics
Rem ********************************************************************

insert into stats$end_event select * from v$system_event;
insert into stats$end_latch select * from v$latch;
insert into stats$end_roll select * from v$rollstat;
insert into stats$end_file select * from stats$file_view;
insert into stats$end_stats select * from v$sysstat;
insert into stats$end_dc select * from v$rowcache;
insert into stats$end_lib select * from v$librarycache;
insert into stats$dates
   select to_char(sysdate, 'dd-mon-yy hh24:mi:ss') from dual;


Rem ********************************************************************
Rem                Create Summary Tables
Rem ********************************************************************

drop table stats$stats;
drop table stats$latches;
drop table stats$roll;
drop table stats$files;
drop table stats$dc;
drop table stats$lib;
drop table stats$event;

create table stats$stats as
select  e.value-b.value change , n.name
   from v$statname n ,  stats$begin_stats b , stats$end_stats e
    where n.statistic# = b.statistic# and n.statistic# = e.statistic#;

create table stats$latches as
select     e.gets-b.gets gets,
    e.misses-b.misses misses,
    e.sleeps-b.sleeps sleeps,
    e.immediate_gets-b.immediate_gets immed_gets,
    e.immediate_misses-b.immediate_misses immed_miss,
    n.name
   from v$latchname n ,  stats$begin_latch b , stats$end_latch e
    where n.latch# = b.latch# and n.latch# = e.latch#;

create table stats$event as
  select  e.total_waits-b.total_waits event_count,
          e.time_waited-b.time_waited time_waited,
          e.event
    from  stats$begin_event b , stats$end_event e
    where b.event = e.event
  union
  select  e.total_waits event_count,
          e.time_waited time_waited,
          e.event
    from  stats$end_event e
    where e.event not in (select b.event from stats$begin_event b);

create table stats$roll as
select  e.usn undo_segment,
        e.gets-b.gets trans_tbl_gets,
    e.waits-b.waits trans_tbl_waits,
    e.writes-b.writes undo_bytes_written,
    e.rssize segment_size_bytes,
        e.xacts-b.xacts xacts,
    e.shrinks-b.shrinks shrinks,
        e.wraps-b.wraps wraps
   from stats$begin_roll b, stats$end_roll e
        where e.usn = b.usn;

create table stats$files as
select b.ts table_space,
       b.name file_name,
       e.pyr-b.pyr phys_reads,
       e.pbr-b.pbr phys_blks_rd,
       e.prt-b.prt phys_rd_time,
       e.pyw-b.pyw phys_writes,
       e.pbw-b.pbw phys_blks_wr,
       e.pwt-b.pwt phys_wrt_tim
  from stats$begin_file b, stats$end_file e
       where b.name=e.name;

create table stats$dc as
select b.parameter name,
       e.gets-b.gets get_reqs,
       e.getmisses-b.getmisses get_miss,
       e.scans-b.scans scan_reqs,
       e.scanmisses-b.scanmisses scan_miss,
       e.modifications-b.modifications mod_reqs,
       e.count count,
       e.usage cur_usage
  from stats$begin_dc b, stats$end_dc e
       where b.cache#=e.cache#
        and  nvl(b.subordinate#,-1) = nvl(e.subordinate#,-1);

create table stats$lib as
select e.namespace,
       e.gets-b.gets gets,
       e.gethits-b.gethits gethits,
       e.pins-b.pins pins,
       e.pinhits-b.pinhits pinhits,
       e.reloads - b.reloads reloads,
       e.invalidations - b.invalidations invalidations
  from stats$begin_lib b, stats$end_lib e
       where b.namespace = e.namespace;


Rem *******************************************************************
Rem              Output statistics
Rem *******************************************************************

spool report.txt;

set charwidth 12
set numwidth 10
Rem Select Library cache statistics.  The pin hit rate shoule be high.
select namespace library,
       gets,
       round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
          gethitratio,
       pins,
       round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
          pinhitratio,
       reloads, invalidations
  from stats$lib;

set charwidth 27;
set numwidth 12;
Rem The total is the total value of the statistic between the time
Rem bstat was run and the time estat was run.  Note that the estat
Rem script logs on as "internal" so the per_logon statistics will
Rem always be based on at least one logon.
select n1.name "Statistic",
       n1.change "Total",
       round(n1.change/trans.change,2) "Per Transaction",
       round(n1.change/logs.change,2)  "Per Logon"
   from stats$stats n1, stats$stats trans, stats$stats logs
   where trans.name='user commits'
    and  logs.name='logons cumulative'
    and  n1.change != 0
   order by n1.name;

set charwidth 27;
set numwidth 12;
Rem System wide wait events.
select     n1.event "Event Name",
           n1.event_count "Count",
    n1.time_waited "Total Time",
    (n1.time_waited/n1.event_count) "Average Time"
   from stats$event n1
   where n1.event_count > 0
   order by n1.time_waited desc;

set numwidth 27
Rem Average length of the dirty buffer write queue.  If this is larger than
Rem the value of the db_block_write_batch init.ora parameter, then consider
Rem increasing the value of db_block_write_batch and check for disks that
Rem are doing many more IOs than other disks.
select queue.change/writes.change "Average Write Queue Length"
  from stats$stats queue, stats$stats writes
 where queue.name  = 'summed dirty queue length'
  and  writes.name = 'write requests';

set charwidth 48;
set numwidth 12;
Rem I/O should be spread evenly accross drives. A big difference between
Rem phys_reads and phys_blks_rd implies table scans are going on.
select * from
  stats$files order by table_space, file_name;

set charwidth 80
Rem sum over tablespaces
select
  table_space||'                                                 '
     table_space,
  sum(phys_reads) phys_reads,  sum(phys_blks_rd) phys_blks_rd,
  sum(phys_rd_time) phys_rd_time,  sum(phys_writes) phys_writes,
  sum(phys_blks_wr) phys_blks_wr,  sum(phys_wrt_tim) phys_wrt_tim
 from stats$files
 group by table_space
 order by table_space;

set charwidth 18;
set numwidth 11;
Rem Sleeps should be low.  The hit_ratio should be high.
select name latch_name, gets, misses,
    round(decode(gets-misses,0,1,gets-misses)/decode(gets,0,1,gets),3)
      hit_ratio,
    sleeps,
    round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
   from stats$latches
    where gets != 0
    order by name;

set numwidth 18
Rem Statistics on no_wait gets of latches.  A no_wait get does not wait for the
Rem latch to become free, it immediately times out.
select name latch_name,
    immed_gets nowait_gets,
    immed_miss nowait_misses,
    round(decode(immed_gets-immed_miss,0,1,immed_gets-immed_miss)/
           decode(immed_gets,0,1,immed_gets),
          3)
      nowait_hit_ratio
   from stats$latches
    where immed_gets != 0
    order by name;


set numwidth 19;
Rem Waits_for_trans_tbl high implies you should add rollback segments.
select * from stats$roll;

set charwidth 39
Rem The init.ora parameters currently in effect:
select name, value from v$parameter where isdefault = 'FALSE' order by name;

set charwidth 15;
set numwidth 8;
Rem get_miss and scan_miss should be very low compared to the requests.
Rem cur_usage is the number of entries in the cache that are being used.
select * from stats$dc
 where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0;


set charwidth 20
Rem The times that bstat and estat were run.
select * from stats$dates;

spool off;

Rem ********************************************************************
Rem                 Drop Temporary Tables
Rem ********************************************************************

drop table stats$dates;

drop table stats$begin_stats;
drop table stats$end_stats;
drop table stats$stats;

drop table stats$begin_latch;
drop table stats$end_latch;
drop table stats$latches;

drop table stats$begin_roll;
drop table stats$end_roll;
drop table stats$roll;

drop table stats$begin_file;
drop table stats$end_file;
drop table stats$files;
drop view stats$file_view;

drop table stats$begin_dc;
drop table stats$end_dc;
drop table stats$dc;

drop table stats$begin_lib;
drop table stats$end_lib;
drop table stats$lib;

drop table stats$begin_event;
drop table stats$end_event;
drop table stats$event;